/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nc.kyiv.yellow.sociallibrary.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author My
 */
public class UserDAO implements IUserDAO {

    DataSource source = null;

    public UserDAO() throws NamingException, Exception {
        if (source != null) {
            return;
        }
        try {
            Context ic = new InitialContext();
            source = (DataSource) ic.lookup("jdbc/sociallibraryConnection");
        } catch (NamingException ex) {
            throw ex;
        }
        if (source == null) {
            throw new Exception("Lookup failed");
        }
    }

    @Override
    public LibUser getUserByLoginAndPass(Map params) {
        Connection conn = null;
        LibUser user = null;
        String query = "SELECT id, role, login, first_name, pass FROM lib_user WHERE login = ? AND pass = ?";
        try {
            conn = getConnection(source);
            PreparedStatement preparedStatement = conn.prepareStatement(query);
            preparedStatement.setString(1, params.get("login").toString());
            preparedStatement.setString(2, params.get("pass").toString());

            ResultSet rs = preparedStatement.executeQuery();

            if (rs.next()) {
                user = new LibUser(rs.getString("login"), rs.getString("first_name"), rs.getString("pass"));
                user.setRole((int) rs.getDouble("role"));
                user.setId((int) rs.getDouble("id"));
            } else {
                user = null;
            }
        } catch (SQLException ex) {
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
        return user;
    }

    @Override
    public void insertOrUpdate(LibUser user) {
        Map loginAndPass = new HashMap<String, String>();
        loginAndPass.put("login", user.getLogin());
        loginAndPass.put("pass", user.getPass());

        if (getUserByLoginAndPass(loginAndPass) != null) {
            update(user);
        } else {
            save(user);
        }
    }

    @Override
    public void save(LibUser user) {
        Connection conn = null;

        StringBuilder query = new StringBuilder();
        query.append("INSERT INTO lib_user (role, login, last_name, first_name, pass, email, status_id, reg_date)");
        query.append("VALUES(");
        query.append("(SELECT id FROM role WHERE role = 'beginner'),"); //role
        query.append("?, ?, ?, ?, ?,"); //login, last_name, first_name, pass, email
        query.append("(SELECT id FROM user_status WHERE status = 'active'),"); //status_id
        query.append("TO_DATE(sysdate, 'DD-MM-YYYY'))"); //reg_date

        try {
            conn = getConnection(source);
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setString(1, user.getLogin());
            preparedStatement.setString(2, user.getLastName());
            preparedStatement.setString(3, user.getFirstName());
            preparedStatement.setString(4, user.getPass());
            preparedStatement.setString(5, user.getEmail());

            preparedStatement.execute();

        } catch (SQLException ex) {
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
    }

    @Override
    public void delete(LibUser t) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void update(LibUser user) {
        Connection conn = null;

        StringBuilder query = new StringBuilder();
        query.append("UPDATE lib_user ");
        query.append("SET last_name = ?,");
        query.append("first_name = ?,");
        query.append("email = ?");
        query.append("WHERE login = ?");

        try {
            conn = getConnection(source);
            PreparedStatement preparedStatement = conn.prepareStatement(query.toString());
            preparedStatement.setString(1, user.getLastName());
            preparedStatement.setString(2, user.getFirstName());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setString(4, user.getLogin());

            preparedStatement.execute();
        } catch (SQLException ex) {
        } finally {
            if (conn != null) {
                releaseConnection(conn);
            }
        }
    }

    @Override
    public Connection getConnection(DataSource source) {
        try {
            Connection conn = source.getConnection();
            conn.setAutoCommit(false);
            return conn;

        } catch (SQLException ex) {
            return null;
        }
    }

    @Override
    public void releaseConnection(Connection connection) {
        try {
            connection.close();
        } catch (SQLException ex) {
        }
    }
}
